﻿using DocumentFormat.OpenXml.ExtendedProperties;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using StudentInformationNOPI.Model;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace StudentInformationNOPI.Services
{

    /// <summary>
    /// 学生信息操作类
    /// </summary>
    class StudentServices
    {
        //读取excel表格
        public static List<Student> ReadFromExcel()
        {
            var students = new List<Student>();
            IWorkbook studentlist;
            //建立一个文件流
            FileStream excel = File.OpenRead(@"StudentData.xlsx");

            //把xlsx中数据写入list
            studentlist = new XSSFWorkbook(excel);
            //读取当前表数据
            ISheet studentsheet = studentlist.GetSheetAt(0);
            //读取当前行数据
            
            for (int i = 1; i < studentsheet.LastRowNum + 1; i++)
            {
                //读取当前行的数据
                IRow row = studentsheet.GetRow(i);
                if (row != null)
                {
                    string[] data = new string[row.LastCellNum + 1];
                    //LastCellNum是当前行的总列数
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            //读取该行的第j列数据
                            string value = row.GetCell(j).ToString();
                            data[j] = value;
                        }
                    }
                    students.Add(new Student(data[0], data[1], data[2], data[3],data[4]));
                }
            }
            return students;
        }

        //计算平均值，找出最大值和最小值,Lambda表达式
        public static void DataProcessing()
        {
            List<Student> students = ReadFromExcel();

            //表格没有数据
            if (!students.Any())
                Console.WriteLine("There is no record!");

            else
            {
                //计算平均分，找出最高分最低分
                double average = students.Average(students => Convert.ToDouble(students.Score));
                double max = students.Max(students => Convert.ToDouble(students.Score));
                double min = students.Min(students => Convert.ToDouble(students.Score));

                //找出最高分最低分对应的同学
                Student topstudent = students[0], loweststudent = students[0];
                foreach (var x in students)
                {
                    if (x.Score == Convert.ToString(max))
                        topstudent = x;
                    if (x.Score == Convert.ToString(min))
                        loweststudent = x;
                }

                //输出
                Console.WriteLine($"这个班的平均分是{average}");
                Console.WriteLine("-----------------------------------------------------------------------------");
                Console.WriteLine($"最高分同学的信息：学号:{topstudent.StudentID}；姓名:{topstudent.Name}；成绩:{topstudent.Score}分；手机号：{topstudent.PhoneNumber}");
                Console.WriteLine($"最低分同学的信息：学号:{loweststudent.StudentID}；姓名:{loweststudent.Name}；成绩:{loweststudent.Score}分；手机号：{topstudent.PhoneNumber}");
            }
        }
    }
}